<?php
/*he 园区学生成绩导入操作*/
class DefaultController extends FController {

    public $user_school_list;
    public $user_subject_list;
    public $user_role_list;
    public $exam_id;
	public function init() {
		parent::init();
		if (empty($this->cLoginUser)) {
			$this->redirect($this->createUrl('/site/login'));
        } else {
            $userLoginUser = $this->cLoginUser;
            $this->user_role_list = isset($userLoginUser['cuser']['role_id']) ? $userLoginUser['cuser']['role_id'] : '';
            $this->user_school_list = isset($userLoginUser['cuser']['school']['school_id']) ? $userLoginUser['cuser']['school']['school_id'] : '';
            $this->user_subject_list = isset($userLoginUser['cuser']['school']['subject_id']) ? $userLoginUser['cuser']['school']['subject_id'] : '';
            $zRow = new ZRole();
            //得到学校对应的考试
            $exam_list = $zRow->_getSchoolExam($this->user_school_list);
            if (!empty($exam_list)) {
                $this->exam_id = implode(',', $exam_list);
            }
		}
		$this->commonScript();
		Yii::app()->clientScript->registerCssFile(THEME_CSS_URL . '/base.css');
		Yii::app()->clientScript->registerCssFile(THEME_CSS_URL . '/master.css');
		Yii::app()->clientScript->registerCssFile(THEME_CSS_URL . '/module_Sip.css');
	}
	//导入
	public function actionIndex() {
        //Yii::app()->clientScript->registerCssFile(STATICS_URL . '/js/ajaxfileupload.css');
        Yii::app()->clientScript->registerScriptFile(STATICS_URL . "/js/ajaxfileupload.js");
		$this->title='园区考试成绩导入';
        $where = $qWhere = '';
                //权限判断
                if ($this->user_role_list != 1 && !empty($this->exam_id)) {
                    $where = ' AND "exam_id" IN (' . $this->exam_id . ')';
            $zExam = new zExam();
            $qWhere = $zExam->_getSchoolInfoList($this->user_school_list);
                }
        $date['ztask'] = ZTask::_getExamAll($where);
		$date['nojoin'] = Nojoin::_getNojoinAll();
		$limit = 8;
		$date['exam_id'] = $this->request->getParam('exam_id',0);
        $data_exam = $data = $pages = array();
        if ($date['exam_id'] > 0) {
		$sql = 'SELECT *  FROM "{{import_error}}" WHERE "type"=3 AND "exam_id"='.$date['exam_id'];
		$count = 'SELECT count("id") FROM "{{import_error}}" WHERE  "type"=3 AND "exam_id"='.$date['exam_id'];
		$data = ZCommon::getDaoPageList($sql, $count, $limit);
            if (!empty($data)) {
                $pages = isset($data['pages']) ? $data['pages'] : '';
            } else {
                $data_exam = self::_GetExamList($date['exam_id'],$qWhere);
                $pages = isset($data_exam['pages']) ? $data_exam['pages'] : '';
            }
        } else {
            $data_exam = self::_GetExamList($date['exam_id'],$qWhere);
            $pages = isset($data_exam['pages']) ? $data_exam['pages'] : '';
        }
		$pageUrl = $this->createUrl('/scores/default/index',array('exam_id'=>$date['exam_id']));
		$this->render('import', array(
            'data' => isset($data['listDatas']) ? $data['listDatas'] : '',
            'pages' => $pages,
            'data_exam' => isset($data_exam['listDatas']) ? $data_exam['listDatas'] : '',
            'pageUrl' => $pageUrl,
            'date' => $date
		));exit;
	}

	//上传文件
	public function actionUpload(){
        // 请求参数
        $isAjax = isset($_POST['isajax'])?$_POST['isajax']:0;
        $examID = isset($_POST['exam_id'])?$_POST['exam_id']:0;
        $subjectID = isset($_POST['subject_id'])?$_POST['subject_id']:0; 
        if($isAjax!=1) exit;
        
        // 表单必填项验证
        if (!$examID || empty($_FILES['file']['name']) || !$subjectID) {
            $this->ajaxReturn(201, '考试项目,导入文件,和科目为必选项');
        }
        
        // 参加本次考试的学校
        $exam_where = " WHERE \"exam_id\"= '{$examID}' ";
        $exam_school = Nojoin::_getexam_schoolALL($exam_where);
        $exam_school_id = array();
        if(empty($exam_school)){
            $this->ajaxReturn(202, '该考试项目没有包含任何学校');
        }
        foreach ($exam_school as $k=>$v){
            $exam_school_id[] = $v['school_id'];
        }
        
        // 学年段判断
        $examSchoolType = zExam::getExamEduType($examID);
        if($examSchoolType==1){
            $titleLine = 2;
        }elseif($examSchoolType==2){
            $titleLine = 1;
        }else{
            $this->ajaxReturn(203, '考试学年段错误');
        }
        
        // 考试科目是否存在
        $subject_where = " WHERE \"subject_id\" = '{$subjectID}' ";
        $subjectinfo = Nojoin::_getsubjectAll($subject_where);
        if(empty($subjectinfo)){
            $this->ajaxReturn(225, '系统中没有此科目');
        }
        
        // 科目分数字段
        $SubjectCode = SmallProblem::pinyin($subjectinfo[0]['subject_name']).'f';

        // 得到学生信息
        $userinfo = $this->cLoginUser;
        $userid = $userinfo['cuser']['id'];//echo $userid;exit;
        $studentinfos = Import::_getstudentinfo($userid);
        if ($userinfo['cuser']['role_id']!=1){
            if (empty($studentinfos)){
                $this->ajaxReturn(204, '当前用户没有可导入的学校');
            }
        }
        
        // 保存excel
        $excelFileTmp =  $_FILES['file']['tmp_name'];
        $uniqueName = ZFileUpload::getUniqueID();     # 得到唯一名称
        $pickFilePath = trim(ZScoreImport::getExcelSavePath(),"\\/")."/".$uniqueName.".pick";
        $scoreImport =  new ZPScoreImport($excelFileTmp, $titleLine);
        $excelInfo = $scoreImport->pickExcel($excelFileTmp, $pickFilePath);//->pickExcel($excelFileTmp, $savePath);
        $ExcelRowCount = $excelInfo['rowNum'];
        if($excelInfo===false){
            $this->ajaxReturn(206,"您上传的不是excel文件");
        }
        if($ExcelRowCount < $titleLine){
            $this->ajaxReturn(206,"无考试成绩数据");
        }
        // 数据库连接
        $conn = Yii::app()->db;
        
        // excel 必须列存在
        $excelErrorMsg = "";
        $missFixedCol = null;
        $excelTH = $excelInfo['title'];
        $fixedCol = ZPScoreImport::getFixedCol($examSchoolType);
        foreach($fixedCol AS $fk => $fv){ // 固定学生信息列
            if(($qindex = array_search($fv, $excelTH))===false){
                $missFixedCol[] = $fv;
            }else{
                unset($excelTH[$qindex]);
            }
        }
        $excelErrorMsg .= $missFixedCol?"缺少考生信息列(".implode(',', $missFixedCol).")":"";
        
        // 小题分列是否存在
        $missQuestionCol = null;
        $teacher_numsql = 'SELECT * FROM "{{teacher_num}}" WHERE "exam_id"=' . $examID . ' AND "subject_id"=' . $subjectID;
        $teacher_numdata = $conn->createCommand($teacher_numsql)->queryAll();
        
        foreach($teacher_numdata as $tvK => $tValue){ 
            if( ($qindex = array_search($tValue['num_id'],$excelTH))===false ){
                $missQuestionCol[] =  $tValue['num_id'];
            }else{
                unset($excelTH[$qindex]);
            }
        }
        $excelErrorMsg .= $missQuestionCol?"缺少小题分列(".implode(',', $missQuestionCol).")":"";
        
        // 无效列
        $invalidCol = array("试卷","区县","年级","班级","客观分","主观分","总分","全卷","1卷","2卷");
        foreach($invalidCol AS  $iv){
            if(($qindex = array_search($iv, $excelTH))!==false)
                unset($excelTH[$qindex]);
        }
        $excelErrorMsg .= $excelTH?"小题分列未设置(".implode(',', $excelTH).")":"";
        if($excelErrorMsg){
            $this->ajaxReturn(211,$excelErrorMsg);
        }
        
        // ajax 返回上传结果
        $salt = md5($uniqueName.$SubjectCode.$examSchoolType.$examID.$subjectID."1"."0".$ExcelRowCount);
        $this->ajaxReturn(1,"文件上传成功",array(
            'f'     =>  urlencode(base64_encode($uniqueName)),
            'sc'    =>  urlencode(base64_encode($SubjectCode)),
            'st'    =>  urlencode(base64_encode($examSchoolType)),
            'eid'   =>  $examID,
            'sid'   =>  $subjectID,
            'bt'    =>  1,
            'he'    =>  0,
            'salt'  =>  $salt,
            'ct'    =>  $ExcelRowCount
        ));
    }
    public function actionVerify(){
        // request 参数
        $saveName = urldecode(base64_decode($this->request->getParam('f','')));
        $SubjectCode = urldecode(base64_decode($this->request->getParam('sc','')));
        $examSchoolType = urldecode(base64_decode($this->request->getParam('st','')));
        $examID = $this->request->getParam('eid','');
        $subjectID = $this->request->getParam('sid','');
        $batch = intval($this->request->getParam('bt',"0"));
        $hasError = $this->request->getParam('he',"0");
        $reqSalt = $this->request->getParam('salt','');
        $rcount   = $this->request->getParam('ct',0);
        $salt = md5($saveName.$SubjectCode.$examSchoolType.$examID.$subjectID.$batch.$hasError.$rcount);
        $sqlFile = $saveName."_{$batch}_.batch";
        if(empty($saveName) || empty($examSchoolType) || empty($examID) || empty($subjectID) || empty($reqSalt) || $batch<1)
            $this->ajaxReturn(201,'校验失败');
        if($reqSalt !== $salt)
            $this->ajaxReturn(202,'校验失败');
        $pickPath = trim(ZScoreImport::getExcelSavePath(),"\\/").DIRECTORY_SEPARATOR.$saveName.".pick";
        if(!file_exists($pickPath)){
            $this->ajaxReturn(203,'校验失败');
        }
        
        
        // 考试学校类型 1 初中，2 高中
        switch ($examSchoolType){
            case 1:
                $titleColNum = 2;
                break;
            case 2:
                $titleColNum = 1;     #标题行
                break;
            default:
                $this->ajaxReturn(213,'考试学校类型错误');
                break;
        }
        $scoreImport =  new ZPScoreImport($pickPath, $titleColNum);
        $excelBatch = $scoreImport->fetchPickBatch($batch,$pickPath,ZPScoreImport::BATCHNUM);
        
        // 数据库连接
        $conn = Yii::app()->db;
        
        // excel 必须列和小题分列是否都存在
        $excelTH = $excelBatch['title'];
        $fixedCol = ZPScoreImport::getFixedCol($examSchoolType);
        $excelExamCodeColIndex = array_search($fixedCol['exam_code'], $excelTH);
        $excelSchoolNameColIndex =   array_search($fixedCol['school_name'], $excelTH);
        $excelStudentNameColIndex =   array_search($fixedCol['student_name'], $excelTH);
        
        //判断当前题号是否存在
        $quesNameTypeMap = array();#汇总方式
        $teacher_numsql = 'SELECT * FROM "{{teacher_num}}" WHERE "exam_id"=' . $examID . ' AND "subject_id"=' . $subjectID;
        $teacher_numdata = $conn->createCommand($teacher_numsql)->queryAll();
        $subPointTH = array();
        foreach($teacher_numdata as $tValue){
            $quesNameTypeMap[$tValue['id']] = $tValue['num_type'];
            $subPointTH[array_search($tValue['num_id'],$excelTH)] = $tValue['id'];
        }
        
        
        
        // 每500行分为一批导入
        $excelData = $excelBatch['data'];
        $student_score_error = array();//错误信息
        $rowCount = count($excelData);
        
        
        
        // 分批验证将验证后的数据转换为sql存入文件后续导入使用
        $student_score = array();//学生成绩信息
        $student_point_info = array();//小题分信息
        $exam_codes = array();
        $dbstudent = null;
        $dbpoint = null;
        
        // excel 对应数据库中的学生数据
        foreach($excelData as $k=>$v){
            $exam_codes[] = preg_replace('/[\s| ]+/', '', $v[$excelExamCodeColIndex]);
        }
        $inStr = '';
        if($exam_codes){
            $exacodestr = "'".implode("','", $exam_codes)."'";
            $inStr = "\"exam_code\" IN ($exacodestr)";
        }
        $sql = "SELECT \"exam_code\",\"student_name\",\"school_name\",\"school_id\",\"student_id\"
                FROM \"{{student}}\" 
                WHERE {$inStr} AND \"exam_id\"='{$examID}'";
        $db_student_rows = $conn->createCommand($sql)->queryAll();
        if($db_student_rows){
            foreach($db_student_rows AS $k=>$v){
                $suqkey = $v['exam_code'].'_'.$v['school_id'];
                $dbstudent[$suqkey] = $v;
            }
        }
        

        // 验证学生成绩数据是否存在
        $sql = "SELECT \"number\",\"student_id\",\"student_id\",\"school_id\"
                FROM \"{{point}}\" 
                WHERE \"number\" IN ($exacodestr)".' AND "subject_id"='.$subjectID.' AND "exam_id"='.$examID;
        $db_student_rows = $conn->createCommand($sql)->queryAll();
        if($db_student_rows){
            foreach($db_student_rows AS $k=>$v){
                $suqkey = $v['number'].'_'.$v['school_id'];
                $dbpoint[$suqkey] = $v;
            }
        }
        unset($db_student_rows);
        
        // 参加本次考试的学校
        $schoolSql = " SELECT s.\"school_id\",s.\"school_nick\" FROM \"{{exam_school}}\" es
                       LEFT JOIN \"{{school}}\" s ON s.\"school_id\"=es.\"school_id\"
                       WHERE \"exam_id\"= '{$examID}' ";
        $examSchool = $conn->createCommand($schoolSql)->queryAll();
        $exam_school_id = array();
        if(empty($examSchool)){
            $this->ajaxReturn(202, '该考试项目没有包含任何学校');
        }
        foreach ($examSchool as $k=>$v){
            $exam_school_id[$v['school_nick']] = $v['school_id'];
        }
        
        foreach ($excelData as $k=>$v){
            $ExcelRowNum = $k+($batch-1)*ZScoreImport::BATCHNUM+$titleColNum+1;
            //判断数据是否为空
            if (empty($v[$excelExamCodeColIndex]) || empty($v[$excelSchoolNameColIndex]) || empty($v[$excelStudentNameColIndex])){
                $student_score_error[]  = array(
                'excel_row'=>$ExcelRowNum,
                'info'=>'考号,姓名,学校是必填项不能为空'
                );
                continue;
            }
            $exam_code = preg_replace('/[\s| ]+/', '', $v[$excelExamCodeColIndex]);
            $student_name = preg_replace('/[\s| ]+/', '', $v[$excelStudentNameColIndex]);
            $schoolNick = preg_replace('/[\s| ]+/', '', $v[$excelSchoolNameColIndex]);
            
            //该考试项目是否包含此次学校
            if (!isset($exam_school_id[$schoolNick])){
                $student_score_error[]  = array(
                'excel_row'=>$ExcelRowNum,
                'info'=>'该考试项目不包含'.$schoolNick.'学校！'
                );
                continue;
            }
            $schoolID = $exam_school_id[$schoolNick];
            
            
            //判断学生是否存在
            $uqkey = $exam_code.'_'.$schoolID;
            if(isset($dbstudent[$uqkey]) && $student_name==$dbstudent[$uqkey]['student_name']){
                $studentinfo = $dbstudent[$uqkey];
            }else{
                $student_score_error[]  = array(
                    'excel_row'=>$ExcelRowNum,
                    'info'=>'此学生'.$student_name.'没有详细信息'
                );
                continue;
            }
            
            //判断当前用户是否有导入当前学校的权限
            if (!empty($userinfo)){
                //超级管理员可以导入任何学校
                if ($userinfo['cuser']['role_id']!=1){
                    //判断当前用户是否有导入所选学校的权限
                    $school_ids = $studentinfos['school_id'];
                    $school_id = $studentinfo['school_id'];
                    if (!strpos($school_ids,$school_id)) {
                        $student_score_error[]  = array(
                        'excel_row'=>$ExcelRowNum,
                        'info'=>'当前用户没有导入当前学校的权限！'
                        );echo "wu quan xian";exit;
                        continue;
                    }
                }
            }

           
            
            //检测学生成绩是否存在
            //$pointinfo = $pointmodel->find(' "number" =\''.$studentinfo['exam_code'].'\' AND "student_id"='.$studentinfo['student_id'].' AND "school_id"='.$studentinfo['school_id'].' AND "subject_id"='.$subject_id.' AND "exam_id"='.$data['exam_id']);
            if (isset($dbpoint[$uqkey]) && $studentinfo['student_id']==$dbpoint[$uqkey]['student_id']){
                $student_score_error[]  = array(
                'excel_row'=>$ExcelRowNum,
                'info'=>'此学生'.$student_name.'成绩已经存在'
                );
                continue;
            }
            

            // 小题分
            foreach($subPointTH AS $spk=>$spv){
                $questionPoint = $v[$spk];
                if(!empty($questionPoint) && !is_numeric($questionPoint)){
                    $student_score_error[]  = array(
                        'excel_row'=>$ExcelRowNum,
                        'info'=>'小题('.$excelTH[$spk].')分数不是数字'
                    );
                    continue;
                }
                $student_point_info[$spv] = $v[$spk];
            }

             
            $student_score[$k] = array(
            'number'=>$v[$excelExamCodeColIndex],
            'student_id'=>$studentinfo['student_id'],
            'school_id'=>$studentinfo['school_id'],
            'exam_id'=>$examID,
            'subject_id'=>$subjectID,
            'score'=>0,
            'score1'=>0,
            'score2'=>0,
            'create_user'=>$this->cLoginUser['cuser']['id'],
            'create_time'=>time(),
            'sub_point' => serialize($student_point_info)
            );
        }
        
        //添加、更新成绩
        $upsql = '';
        if (!empty($student_score)){
            foreach ($student_score as $k=>$score){
                $tbCols = '"'.implode('","',array_keys($score)).'"';
                $tbValues = "'".implode("','",array_values($score)). "'";
                $upsql .= "INSERT INTO \"{{point}}\" ({$tbCols}) values({$tbValues});
                            ";
            }
            // 批处理sql
            if($upsql){
                $count = count($excelData);
                $upsql = "  
                            --<$count>
                            DECLARE
                            BEGIN
                            {$upsql}
                            END;";
                file_put_contents(UPLOAD_PATH . '/scores/'.$sqlFile, $upsql);
                unset($upsql);
            }
        }
        
        // 错误日志
        $importerror = new ImportError();
        if($batch == 1){
            //将本次项目导入错误日志清空
            $importerror->deleteAll('"type"=3 AND "exam_id"='.$examID);
        }
        if(count($student_score_error)>0){
            foreach ($student_score_error as $k=>$v){
                $importerror = new ImportError();
                $v['type']=3;
                $v['exam_id']=$examID;
                $importerror->attributes=$v;
                $importerror->save();
            }
            $hasError = 1;
        }
        $readStatus = $excelBatch['readEnd'];
        // ajax 返回验证结果
        if($readStatus == 1){
            $batch = 1;
        }else{
            $batch ++;
        }
        $salt = md5($saveName.$SubjectCode.$examSchoolType.$examID.$subjectID.$batch.$hasError.$rowCount);
        $this->ajaxReturn($readStatus,"文件上传成功",array(
            'f'     =>  base64_encode($saveName),
            'sc'    =>  base64_encode($SubjectCode),
            'st'    =>  base64_encode($examSchoolType),
            'eid'   =>  $examID,
            'sid'   =>  $subjectID,
            'bt'    =>  $batch,
            'he'    =>  $hasError,
            'salt'  =>  $salt,
            'ct'    =>  $rowCount
        ));
	}
        // 导入
    public function actionImport(){
        // request 参数
        $saveName = urldecode(base64_decode($this->request->getParam('f','')));
        $SubjectCode = urldecode(base64_decode($this->request->getParam('sc','')));
        $examSchoolType = urldecode(base64_decode($this->request->getParam('st','')));
        $examID = $this->request->getParam('eid','');
        $subjectID = $this->request->getParam('sid','');
        $batch = $this->request->getParam('bt',0);
        $hasError = $this->request->getParam('he',0);
        $reqSalt = $this->request->getParam('salt','');
        $rcount   = $this->request->getParam('ct',0);
        $salt = md5($saveName.$SubjectCode.$examSchoolType.$examID.$subjectID.$batch.$hasError.$rcount);
        $saveNamePathinfo = explode('.', $saveName);
        $sqlFile = UPLOAD_PATH . '/scores/'.$saveNamePathinfo[0]."_{$batch}_.batch";
        if(empty($saveName) || empty($examSchoolType) || empty($examID) || empty($subjectID) || empty($reqSalt) || $batch<1)
            $this->ajaxReturn(201,'校验失败');
        if($reqSalt !== $salt)
            $this->ajaxReturn(202,'校验失败');
        
        if(file_exists($sqlFile)){
            // 数据库连接
            $conn = Yii::app()->db;
            $content = file_get_contents($sqlFile);
            $count = 0;
            if($content){
                $begin  = strpos($content,'<')+1;
                $end  = strpos($content,'>');
                $count = substr($content, $begin, $end-$begin);
                $conn->createCommand($content)->query();
            }
            //@unlink($sqlFile);
            // ajax 返回验证结果
            $batch ++;
            $salt = md5($saveName.$SubjectCode.$examSchoolType.$examID.$subjectID.$batch.$hasError.$count);
            $this->ajaxReturn(2,"文件上传成功",array(
                'f'     =>  base64_encode($saveName),
                'sc'    =>  base64_encode($SubjectCode),
                'st'    =>  base64_encode($examSchoolType),
                'eid'   =>  $examID,
                'sid'   =>  $subjectID,
                'bt'    =>  $batch,
                'he'    =>  $hasError,
                'salt'  =>  $salt,
                'ct'    =>  $count,
            ));
        }
        $this->ajaxReturn(1);
    }
	static function myfunction($v)	{
		return $v['num_id'];
	}
	static function myfunctions($date)	{
		$data = '';
		foreach ($date as $k=>$v){
			$data[$v['num_id']]= array('id'=>$v['id'],'num_id'=>$v['num_id'],'num_type'=>$v['num_type']);
		}
		return $data;
	}
    protected function _GetExamList($exam_id = 0, $qWhere,$pageSize = 8) {
        $conn = Yii::app()->db;
        $where = '';
        if ($exam_id > 0) {
            $where = ' WHERE "exam_id" = ' . $exam_id;
            $where .= $qWhere;
        }else{
            $where = !empty($qWhere) ? " WHERE ".$qWhere : '';
        }
        $sql = 'SELECT "student_id","student_name","exam_id","exam_code","school_name","class_name","socre" FROM "{{student}}" ' . $where;
        $sqlCount = 'SELECT count(*) FROM "{{student}}" ' . $where;
        $data = ZCommon::getDaoPageList($sql, $sqlCount, $pageSize);
        return $data;
    }
    //更新功能
    public function actionUpdate(){
        $this->title = '学生信息设置';
        $this->layout = "//layouts/main_window";
        $data = array();
        $sid = $this->request->getParam('sid');
        $eid = $this->request->getParam('eid');
        $zedu = new ZEdu();
        if($sid>0 && $eid>0){
            $data = $zedu->_getStudentInfo($sid,$eid);
        }
        //保存更新信息
        if (isset($_POST['submit']) && !empty($_POST['submit'])) {
            $status = $zedu->_updstuExamScore($_POST['student_id'],$_POST['exam_id'],$_POST);
            if ($status) {
                $this->frameAjaxMessage('/scores/default/index');
                exit;
            } else {
                $this->frameAjaxMessage(null, '更新失败');
                exit;
            }
        }
        $this->render('update',array(
            'data'  => $data
        ));
    }
    //删除功能
    public function actionDelete(){
        $status = 0;
        $sid = $this->request->getParam('sid');
        $eid = $this->request->getParam('eid');
        if($sid>0 && $eid>0){
            $zedu = new ZEdu();
            $status = $zedu->_delstuScore($sid,$eid);
        }
        echo $status;
    }
    private function ajaxReturn($code,$msg='',$ext=''){
        $res = array('code'=>$code,'msg'=>$msg, 'ext'=>$ext);
        echo  json_encode($res);exit;
    }
}
